package com.ejoysoft.wlanyz.business;

import com.ejoysoft.auth.MD5;
import com.ejoysoft.common.*;
import com.ejoysoft.common.exception.UserUnitIdException;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpSession;

import java.util.ArrayList;
import java.util.Vector;
import java.util.HashMap;
import java.util.Date;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;
import jxl.*;
import java.util.List;
import java.io.*;
import java.util.Random;

public class Task {
    private Globa globa;
    private DbConnect db;
    private DbConnect dd= new DbConnect();
    
    public Task() {
    }
   

    public Task(Globa globa) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
    }

    //??????
    public Task(Globa globa, boolean b) {
        this.globa = globa;
        db = globa.db;
        dd = globa.db;
        if (b) globa.setDynamicProperty(this);
    }

    String strTableName = "wlan_task_info";
    String strTableName1 ="wlan_resc_hotpot ";
    
    //增加用户
    public boolean add(String strTaskMan,String strTaskPhone,String strTaskName,String strTaskType,String strNetType,String dtStartTime,String dtEndTime,String strTaskDes,String strPingAddr,String strHttpSite,String strFtpAddress,String strTestCount,String strRemark,String strHotpotName,String intIssued,String strApIds ) {
        String strSql = "";
        strTaskId = UID.getID();
        try {
        	//把用户信息写入数据库
        	strSql = "INSERT INTO " + strTableName + "  (strTaskId,strTaskMan,strTaskPhone,strTaskName,strTaskType," +
        			"strNetType,dtStartTime,dtEndTime,strTaskDes,strPingAddr,strHttpSite,strFtpAddress,strTestCount," +
        			"strRemark,strHotpotName,intIssued,strCreator, dtCreatDate,strApIds) VALUES (?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss')," +
        			"to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?)";
            db.prepareStatement(strSql);
            db.setString(1, strTaskId);
            db.setString(2, strTaskMan);
            db.setString(3, strTaskPhone);
            db.setString(4, strTaskName);
            db.setString(5, strTaskType);
            db.setString(6, strNetType);
            db.setString(7, dtStartTime);
            db.setString(8, dtEndTime);
            db.setString(9, strTaskDes);
            db.setString(10, strPingAddr);
            db.setString(11, strHttpSite);
            db.setString(12, strFtpAddress);
            db.setString(13, strTestCount);
            db.setString(14, strRemark);
            db.setString(15, strHotpotName);
            db.setString(16, intIssued);
            db.setString(17, globa.loginName);
            db.setString(18, Format.getDateTime());
            db.setString(19, strApIds);
   
            if (db.executeUpdate() > 0) {
                Globa.logger0("增加测试项", globa.loginName, globa.loginIp, strSql, "任务管理", globa.userSession.getStrDepart());
                return true;
            } else
                return false;
        } catch (Exception e) {
            System.out.println("增加用户信息时出错！！");
            e.printStackTrace();
            return false;
        }
    }

   

    //?删除部门用户部门联系表中的一条数据
    public boolean delete(String where) {
        try {
            String sql = "DELETE FROM " + strTableName + "  ".concat(where);
            db.executeUpdate(sql);
            return true;
        } catch (Exception ee) {
            ee.printStackTrace();
            return false;
        }
    }
    
 
    public boolean update(String strTaskId,String strTaskMan,String strTaskPhone,String strTaskName,String strTaskType,String strNetType,String dtStartTime,String dtEndTime,String strTaskDes,String strPingAddr,String strHttpSite,String strFtpAddress,String strTestCount,String strRemark,String strHotPotName,String intIssued,String strApIds) {
        try {
        	
        	if (intIssued==null)
        		intIssued="0";
        	if(dtStartTime.length()>0)
        	{
        		dtStartTime=dtStartTime.substring(0,dtStartTime.length()-2);	
        	}
        	if(dtEndTime.length()>0)
        	{
        		dtEndTime=dtEndTime.substring(0,dtEndTime.length()-2);	
        	}
        	
        	String strSql = "UPDATE  " + strTableName + "  SET strTaskMan=?,strTaskPhone=?,strTaskName=?,strTaskType=?,strNetType=?,dtStartTime=to_date(?,'yyyy-MM-dd hh24:mi:ss'),dtEndTime=to_date(?,'yyyy-MM-dd hh24:mi:ss'),strTaskDes=?,strPingAddr=?,strHttpSite=?,strFtpAddress=?,strTestCount=?,strRemark=?,strHotPotName=?,intIssued=?, strCreator = ?, dtCreatDate = to_date(?,'yyyy-MM-dd hh24:mi:ss') ,strApIds = ? WHERE strTaskId=? ";

            
            db.prepareStatement(strSql);
            db.setString(1, strTaskMan);
            db.setString(2, strTaskPhone);
            db.setString(3, strTaskName);
            db.setString(4, strTaskType);
            db.setString(5, strNetType);
            db.setString(6, dtStartTime);
            db.setString(7, dtEndTime);
            db.setString(8, strTaskDes);
            db.setString(9, strPingAddr);
            db.setString(10, strHttpSite);
            db.setString(11, strFtpAddress);
            db.setString(12, strTestCount);
            db.setString(13, strRemark);
            db.setString(14, strHotPotName);
            db.setString(15, intIssued);
            db.setString(16, globa.loginName);
            db.setString(17, Format.getDateTime());
            db.setString(18, strApIds);
            db.setString(19, strTaskId);
            db.executeUpdate();
            Globa.logger0("修改任务信息", globa.loginName, globa.loginIp, strSql, "任务管理", globa.userSession.getStrDepart());
           
            return true;
        } catch (Exception e) {
            System.out.println("修改测试项信息时出错：" + e);
            return false;
        }
    }
    public boolean add1(String strTaskId,String strApIds,String intIssued) {
        try {
        	String strSql = "UPDATE  " + strTableName + "  SET strApIds = ? , intIssued = ? WHERE strTaskId = ? ";

           // System.out.println("2222222222222222222222"+intIssued);
            db.prepareStatement(strSql);
            
            db.setString(1, strApIds);
            db.setString(2, intIssued);
            db.setString(3, strTaskId);
            db.executeUpdate();
            Globa.logger0("修改测试项信息", globa.loginName, globa.loginIp, strSql, "任务管理", globa.userSession.getStrDepart());
            return true;
        } catch (Exception e) {
            System.out.println("修改测试项信息时出错：" + e);
            return false;
        }
    }
  //详细显示单条记录
    public Task show(String where) {
        try {
            String strSql = "select * from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }

  //记录集转化为对象
   
  //查询符合条件的记录总数
    public int getCount(String where) {
        int count = 0;
        try {
            String sql = "SELECT count(strTaskId) FROM " + strTableName + "  ";
            if (where.length() > 0) {
                where = where.toLowerCase();
                if (where.indexOf("order") > 0)
                    where = where.substring(0, where.lastIndexOf("order"));
                sql = String.valueOf(sql) + String.valueOf(where);
            }
            ResultSet rs = db.executeQuery(sql);
            if (rs.next())
                count = rs.getInt(1);
            rs.close();
            return count;
        } catch (Exception ee) {
            ee.printStackTrace();
            return count;
        }
    }

  //列表记录集
	public Vector<Task> list(String where, int startRow, int rowCount) {
		Vector<Task> beans = new Vector<Task>();
        try {
            String sql = "SELECT *  FROM  " + strTableName + " ";
            //System.out.println("tttttttttttttttt"+sql);
            if (where.length() > 0)
                sql = String.valueOf(sql) + String.valueOf(where);
            Statement s = db.getConnection().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
            if (startRow != 0 && rowCount != 0)
                s.setMaxRows((startRow + rowCount) - 1);
            ResultSet rs = s.executeQuery(sql);
            if (rs != null && rs.next()) {
                if (startRow != 0 && rowCount != 0)
                    rs.absolute(startRow);
                do {
                	Task theBean = new Task();
                    theBean = load(rs, false);
                    beans.addElement(theBean);
                } while (rs.next());
            }
            rs.close();
            s.close();
        } catch (Exception ee) {
            ee.printStackTrace();
        }
        return beans;
    }

	 public Task load(ResultSet rs, boolean isView) {
		 Task theBean = new Task();
	        try {
	            theBean.setStrTaskId(rs.getString("strTaskId"));
	            theBean.setStrTaskMan(rs.getString("strTaskMan"));
	            theBean.setStrTaskPhone(rs.getString("strTaskPhone"));
	            theBean.setStrTaskName(rs.getString("strTaskName"));
	            theBean.setStrTaskType(rs.getString("strTaskType"));
	            theBean.setStrNetType(rs.getString("strNetType"));
	            theBean.setDtStartTime(rs.getString("dtStartTime"));
	            theBean.setDtEndTime(rs.getString("dtEndTime"));
	            theBean.setStrTaskDes(rs.getString("strTaskDes"));
	            theBean.setStrPingAddr(rs.getString("strPingAddr"));
	            theBean.setStrHttpSite(rs.getString("strHttpSite"));
	            theBean.setStrFtpAddress(rs.getString("strFtpAddress"));
	            theBean.setStrTestCount(rs.getInt("strTestCount"));
	            theBean.setStrRemark(rs.getString("strRemark"));
	            theBean.setStrHotpotName(rs.getString("strHotpotName"));
	            theBean.setIntIssued(rs.getInt("intIssued"));
	            theBean.setStrCreator(rs.getString("strCreator"));
	            theBean.setDtCreatDate(rs.getString("dtCreatDate"));
	            theBean.setStrApIds(rs.getString("strApIds"));

	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        return theBean;
	    }


    public Task(String strTaskId,String strTaskMan,String strTaskPhone,String strTaskName,String strTaskType,String strNetType,String dtStartTime,String dtEndTime,String strTaskDes,String strPingAddr,String strHttpSite,String strFtpAddress,int strTestCount,String strRemark,String strHotpotName,int intIssued,String strCreator ,String dtCreatDate,String strApIds) 
    {
    	this.strTaskId=strTaskId;
    	this.strTaskMan=strTaskMan;
    	this.strTaskPhone=strTaskPhone;
    	this.strTaskName=strTaskName;
    	this.strTaskType=strTaskType;
    	this.strNetType=strNetType;
    	this.dtStartTime=dtStartTime;
    	this.dtEndTime=dtEndTime;
    	this.strTaskDes=strTaskDes;
    	this.strPingAddr=strPingAddr;
    	this.strHttpSite=strHttpSite;
    	this.strFtpAddress=strFtpAddress;
    	this.strTestCount=strTestCount;
    	this.strRemark=strRemark;
    	this.strHotpotName=strHotpotName;
    	this.intIssued=intIssued;
    	this.strCreator=strCreator;
    	this.dtCreatDate=dtCreatDate; 
    	this.strApIds=strApIds;
    }
//*************************************************************************************
    public ArrayList<Object[]> getAllclassoneByName( String where )
    {
    	DbConnect dbConnect = new DbConnect();
    	ArrayList<Object[]>  beans = new ArrayList<Object[]>();
    	String sql = "select distinct(strClassonehp) from "+strTableName1+" ";
    	if(where!=null && where.length()>0)
    		sql = sql +" "+where;
    	ResultSet re = dbConnect.executeQuery(sql);
    	try {
			if(re != null && re.next())
			{
				do{
					Object[] theBean = new Object[2];
					String strClassonehp = re.getString("strClassonehp");
					theBean[0] = strClassonehp;
					theBean[1] = this.getAllstrclasstwoByName(strClassonehp);
					beans.add(theBean);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbConnect.closeCon();
    	return beans;
    }
    public ArrayList<Ac> getAllstrclasstwoByName( String strClassonehp)
    {   DbConnect dbConnect = new DbConnect();
    	ArrayList<Ac>  beans = new ArrayList<Ac>();
    	String sql = "select distinct(strHotpotName),strid from "+strTableName1+" ";
    	String where = " where strClassonehp='"+strClassonehp+"'";
    	sql = sql+where;
    	ResultSet re = dbConnect.executeQuery(sql);
    	try { 
			if(re != null && re.next())
			{   //String strcountry = re.getString("");
				do{
					String strHotpotName = re.getString("strHotpotName");
					Ac obj = new Ac();
					obj.setStrId(re.getString("strId"));
					obj.setStrHotPotName(strHotpotName);
					beans.add(obj);
				}while(re.next());
			}
			re.close();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		dbConnect.closeCon();
    	return beans;
    }
    //***************运行商对比分析********************************
    public Task show1(String where) {
        try {
            String strSql = "select strTaskName,dtStartTime, dtEndTime from  " + strTableName + "  ".concat(where);
            ResultSet rs = db.executeQuery(strSql);
            if (rs != null && rs.next())
                return load1(rs, true);
            else
                return null;
        } catch (Exception ee) {
            return null;
        }
    }
    public Task load1(ResultSet rs, boolean isView) {
		 Task theBean = new Task();
	        try {
	            theBean.setStrTaskName(rs.getString("strTaskName"));
	            theBean.setDtStartTime(rs.getString("dtStartTime"));
	            theBean.setDtEndTime(rs.getString("dtEndTime"));
	        } catch (Exception e) {
	            e.printStackTrace();
	        }
	        return theBean;
	    }
    
    //**************************运行商对比分析**********************
    //********************导入**************************************
    public void insertinto(String s1,String s2,String s3,String s4,String s5,String s6,String s7,String s8,String s9,String s10,String s11,String s12,String s13,String s14,String s15,String s16,String s17,String s18){
    	String strSql = "";
		try {
    		String strTempId = UID.getID();
	        strSql = "INSERT INTO wlan_task_info (strTaskId,strTaskMan,strTaskPhone,strTaskName,strTaskType," +
        			"strNetType,dtStartTime,dtEndTime,strTaskDes,strPingAddr,strHttpSite,strFtpAddress,strTestCount," +
        			"strRemark,strHotpotName,intIssued,strCreator, dtCreatDate,strApIds) VALUES (?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss')," +
        			"to_date(?,'yyyy-MM-dd hh24:mi:ss'),?,?,?,?,?,?,?,?,?,to_date(?,'yyyy-MM-dd hh24:mi:ss'),?)";
	        
	        dd.prepareStatement(strSql);
            dd.setString(1, strTempId);
            dd.setString(2, s1);
            dd.setString(3, s2);
            dd.setString(4, s3);
            dd.setString(5, s4);
            dd.setString(6, s4);
            dd.setString(7, s6);
            dd.setString(8, s7);
            dd.setString(9, s8);
            dd.setString(10, s9);
            dd.setString(11, s10);
            dd.setString(12, s11);
            dd.setString(13, s12);
            dd.setString(14, s13);
            dd.setString(15, s14);
            dd.setString(16, s15);
            dd.setString(17, s16);
            dd.setString(18, Format.getDateTime());
            dd.setString(19, s18);
			
	        if (dd.executeUpdate() > 0) {
	        	Globa.logger0("导入excel表格成功", globa.loginName, globa.loginIp, strSql, "任务管理", globa.userSession.getStrDepart());
	        	
	        } 
		} catch (Exception e) {
			//dd.rollback();
	        e.printStackTrace();
	        //dd.closeCon();
		}
		dd.closeCon();
    }
    public void method(String url){
    	try
	    {
	    List<String> list = new ArrayList<String>();
	    Workbook workBook=Workbook.getWorkbook(new File(url));
	    //获得第一个工作表对象
	    Sheet sheet=workBook.getSheet(0);
	    
	    //得到第一列第一行的单元格
	    Cell cell=null;
	
	    
	    for (int i = 1; i < sheet.getRows(); i++){
	        List<String> list2 = new ArrayList<String>();
	        for (int j = 0; j < sheet.getColumns(); j++) {
	        	cell = sheet.getCell(j, i);
	        	list2.add(cell.getContents().toString());//将每行的字符串用一个String类型的集合保存。
	        }
	        
	        insertinto(list2.get(0),list2.get(1),list2.get(2),list2.get(3),list2.get(4),list2.get(5),list2.get(6),list2.get(7),list2.get(8),list2.get(9),list2.get(10),list2.get(11),list2.get(12),list2.get(13),list2.get(14),list2.get(15),list2.get(16),list2.get(17));
	    }
	    	workBook.close();//记得关闭
	      
	      //迭代集合查看每行的数据
		    
		    
	    }catch(Exception e)
	    {
	    System.out.println(e);
	    }
    }
    
 //***********自动下发产生随机数********************************************
    public List<String> randommm(int num) {
      List<String> list = new ArrayList<String>();
      Random rand = new Random();
	  int randInt1 = 0;
	  randInt1  = 1+rand.nextInt(num);
	  boolean[]  bool = new boolean[num];
      int randInt = 0;
      for(int i = 0; i < randInt1 ; i++) {
    	  do {
    		  randInt  = rand.nextInt(num);

             }while(bool[randInt]);

            bool[randInt] = true;
            list.add(String.valueOf(randInt));
      }
      return list;

  }

    
//**************************************************************************************
    private String strTaskId;
    private String strTaskMan;
    private String strTaskPhone;
    private String strTaskName;
    private String strTaskType;
    private String strNetType;
    private String dtStartTime; 
    private String dtEndTime; 
    private String strTaskDes; 
    private String strPingAddr; 
    private String strHttpSite ;
    private String strFtpAddress; 
    private int strTestCount ;
    private String strRemark ;
    private String strHotpotName; 
    private int intIssued ;
    private String strCreator ;
    private String dtCreatDate;
    private String strApIds;

	public String getStrApIds() {
		return strApIds;
	}


	public void setStrApIds(String strApIds) {
		this.strApIds = strApIds;
	}


	public String getStrTaskId() {
		return strTaskId;
	}

	public void setStrTaskId(String strTaskId) {
		this.strTaskId = strTaskId;
	}

	public String getStrTaskMan() {
		return strTaskMan;
	}

	public void setStrTaskMan(String strTaskMan) {
		this.strTaskMan = strTaskMan;
	}

	public String getStrTaskPhone() {
		return strTaskPhone;
	}

	public void setStrTaskPhone(String strTaskPhone) {
		this.strTaskPhone = strTaskPhone;
	}

	public String getStrTaskName() {
		return strTaskName;
	}

	public void setStrTaskName(String strTaskName) {
		this.strTaskName = strTaskName;
	}

	public String getStrTaskType() {
		return strTaskType;
	}

	public void setStrTaskType(String strTaskType) {
		this.strTaskType = strTaskType;
	}

	public String getStrNetType() {
		return strNetType;
	}

	public void setStrNetType(String strNetType) {
		this.strNetType = strNetType;
	}

	public String getDtStartTime() {
		return dtStartTime;
	}

	public void setDtStartTime(String dtStartTime) {
		this.dtStartTime = dtStartTime;
	}

	public String getDtEndTime() {
		return dtEndTime;
	}

	public void setDtEndTime(String dtEndTime) {
		this.dtEndTime = dtEndTime;
	}

	public String getStrTaskDes() {
		return strTaskDes;
	}

	public void setStrTaskDes(String strTaskDes) {
		this.strTaskDes = strTaskDes;
	}

	public String getStrPingAddr() {
		return strPingAddr;
	}

	public void setStrPingAddr(String strPingAddr) {
		this.strPingAddr = strPingAddr;
	}

	public String getStrHttpSite() {
		return strHttpSite;
	}

	public void setStrHttpSite(String strHttpSite) {
		this.strHttpSite = strHttpSite;
	}

	public String getStrFtpAddress() {
		return strFtpAddress;
	}

	public void setStrFtpAddress(String strFtpAddress) {
		this.strFtpAddress = strFtpAddress;
	}

	public int getStrTestCount() {
		return strTestCount;
	}

	public void setStrTestCount(int strTestCount) {
		this.strTestCount = strTestCount;
	}

	public String getStrRemark() {
		return strRemark;
	}

	public void setStrRemark(String strRemark) {
		this.strRemark = strRemark;
	}

	public String getStrHotpotName() {
		return strHotpotName;
	}

	public void setStrHotpotName(String strHotpotName) {
		this.strHotpotName = strHotpotName;
	}

	public int getIntIssued() {
		return intIssued;
	}

	public void setIntIssued(int intIssued) {
		this.intIssued = intIssued;
	}

	public String getStrCreator() {
		return strCreator;
	}

	public void setStrCreator(String strCreator) {
		this.strCreator = strCreator;
	}

	public String getDtCreatDate() {
		return dtCreatDate;
	}

	public void setDtCreatDate(String dtCreatDate) {
		this.dtCreatDate = dtCreatDate;
	}
    
    
    
    
    
	
    
   

    

    
}
